Week Of: 2022-09-18
22/09/2022

DB-Trigger that creates new profile in ERP for new app. later this profile will be in use for the Authoriation process

Profile creation #

CREATE OR REPLACE TRIGGER OP_DEFINE_PROFILE_TRG
  AFTER INSERT ON APEX_180200.WWV_FLOWS
  FOR EACH ROW
DECLARE
  -- local variables here
  X_ROWID          VARCHAR2(256);
  V_WORKSPACE      VARCHAR2(64);
  V_ERR            VARCHAR2(400);
  V_EXISTS         VARCHAR(1);
  I                NUMBER;
  L_SUFFIX         VARCHAR2(10);
  L_SUFFIX_DISPLAY VARCHAR2(15);
BEGIN
  SELECT FC.SHORT_NAME
    INTO V_WORKSPACE
    FROM APEX_180200.WWV_FLOW_COMPANIES FC
   WHERE FC.PROVISIONING_COMPANY_ID = :NEW.SECURITY_GROUP_ID;
  FOR I IN 1 .. 2 LOOP
    IF I = 1 THEN
      L_SUFFIX         := '_V';
      L_SUFFIX_DISPLAY := ' Viewer';
    ELSE
      L_SUFFIX         := '_A';
      L_SUFFIX_DISPLAY := ' Admin';
    END IF;
    BEGIN
      SELECT 'Y'
        INTO V_EXISTS
        FROM FND_PROFILE_OPTIONS_VL T
       WHERE (T.PROFILE_OPTION_NAME =
             'APEX_' || V_WORKSPACE || '_' || :NEW.DISPLAY_ID || L_SUFFIX OR
             T.USER_PROFILE_OPTION_NAME =
             'APEX: ' || V_WORKSPACE || ' ' || :NEW.DISPLAY_ID ||
             L_SUFFIX_DISPLAY);
    
    EXCEPTION
      WHEN OTHERS THEN
        V_EXISTS := 'N';
    END;
    IF V_WORKSPACE IN ('PO', 'LOG', 'AP', 'GL', 'GENERAL') AND
       V_EXISTS = 'N' THEN
      FND_PROFILE_OPTIONS_PKG.INSERT_ROW(X_ROWID                      => X_ROWID,
                                         X_PROFILE_OPTION_NAME        => 'APEX_' ||
                                                                         V_WORKSPACE || '_' ||
                                                                         :NEW.DISPLAY_ID ||
                                                                         L_SUFFIX,
                                         X_APPLICATION_ID             => 20006,
                                         X_PROFILE_OPTION_ID          => FND_PROFILE_OPTIONS_S.NEXTVAL,
                                         X_WRITE_ALLOWED_FLAG         => 'Y',
                                         X_READ_ALLOWED_FLAG          => 'Y',
                                         X_USER_CHANGEABLE_FLAG       => 'Y',
                                         X_USER_VISIBLE_FLAG          => 'Y',
                                         X_SITE_ENABLED_FLAG          => 'Y',
                                         X_SITE_UPDATE_ALLOWED_FLAG   => 'Y',
                                         X_APP_ENABLED_FLAG           => 'Y',
                                         X_APP_UPDATE_ALLOWED_FLAG    => 'Y',
                                         X_RESP_ENABLED_FLAG          => 'Y',
                                         X_RESP_UPDATE_ALLOWED_FLAG   => 'Y',
                                         X_USER_ENABLED_FLAG          => 'Y',
                                         X_USER_UPDATE_ALLOWED_FLAG   => 'Y',
                                         X_START_DATE_ACTIVE          => SYSDATE,
                                         X_SQL_VALIDATION             => 'SQL="SELECT MEANING \"Yes or No\", LOOKUP_CODE
into :visible_option_value,
:profile_option_value
from fnd_common_lookups
where lookup_type = ''YES_NO''"
COLUMN="\"Yes or No\"(*)"',
                                         X_END_DATE_ACTIVE            => NULL,
                                         X_USER_PROFILE_OPTION_NAME   => 'APEX: ' ||
                                                                         V_WORKSPACE || ' ' ||
                                                                         :NEW.DISPLAY_ID ||
                                                                         L_SUFFIX_DISPLAY,
                                         X_DESCRIPTION                => '',
                                         X_CREATION_DATE              => SYSDATE,
                                         X_CREATED_BY                 => FND_GLOBAL.USER_ID,
                                         X_LAST_UPDATE_DATE           => SYSDATE,
                                         X_LAST_UPDATED_BY            => FND_GLOBAL.USER_ID,
                                         X_LAST_UPDATE_LOGIN          => FND_GLOBAL.USER_ID,
                                         X_HIERARCHY_TYPE             => 'SECURITY',
                                         X_SERVER_ENABLED_FLAG        => 'N',
                                         X_SERVER_UPDATE_ALLOWED_FLAG => 'N',
                                         X_ORG_ENABLED_FLAG           => 'N',
                                         X_ORG_UPDATE_ALLOWED_FLAG    => 'N');
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    V_ERR := SQLERRM;
    INSERT INTO OP_DEBUG (SEQ, MESSAGE) VALUES ('error', V_ERR);
    COMMIT;
  
END OP_DEFINE_PROFILE_TRG;
/

Delete Profile #

CREATE OR REPLACE TRIGGER OP_DELETE_PROFILE_TRG
  BEFORE DELETE ON APEX_180200.WWV_FLOWS
  FOR EACH ROW
DECLARE
  V_WORKSPACE VARCHAR2(64);
  V_ERR       VARCHAR2(400);
  V_EXISTS    VARCHAR(1);
  I           NUMBER;
  L_SUFFIX    VARCHAR2(10);
BEGIN
  SELECT FC.SHORT_NAME
    INTO V_WORKSPACE
    FROM APEX_180200.WWV_FLOW_COMPANIES FC
   WHERE FC.PROVISIONING_COMPANY_ID = :OLD.SECURITY_GROUP_ID;
  FOR I IN 1 .. 2 LOOP
    IF I = 1 THEN
      L_SUFFIX := '_V';
    ELSE
      L_SUFFIX := '_A';
    END IF;
    BEGIN
      SELECT 'Y'
        INTO V_EXISTS
        FROM FND_PROFILE_OPTIONS_VL T
       WHERE (T.PROFILE_OPTION_NAME =
             'APEX_' || V_WORKSPACE || '_' || :OLD.DISPLAY_ID || L_SUFFIX);
    EXCEPTION
      WHEN OTHERS THEN
        V_EXISTS := 'N';
    END;
    IF V_WORKSPACE IN ('PO', 'LOG', 'AP', 'GL', 'GENERAL') AND
       V_EXISTS = 'Y' THEN
    
      FND_PROFILE_OPTIONS_PKG.DELETE_ROW(X_PROFILE_OPTION_NAME => 'APEX_' ||
                                                                  V_WORKSPACE || '_' ||
                                                                  :OLD.DISPLAY_ID ||
                                                                  L_SUFFIX);
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    V_ERR := SQLERRM;
    INSERT INTO OP_DEBUG (SEQ, MESSAGE) VALUES ('error', V_ERR);
    COMMIT;
  
END OP_DELETE_PROFILE_TRG;
/